package cn.lyh97.estore.easystoremanager.Classes;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;


public class SqlHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "goodsDB.db";
    private static final String TABLE_NAME = "goods";
    private static final int VERSION = 1;
/*    private static final String ID ="ID";
    private static final String IMGID ="IMGID";
    private static final String NAME ="NAME";
    private static final String  TAG= "TAG";
    private static final String DESCRIPTION="DESCRIPTION";
    private static final String NUM="NUM";
    private static final String PRICE = "PRICE";*/


    private static final String CREATE_TABLE = "create table if not exists " + TABLE_NAME +
            "( ID varchar(20) primary key," +
            "ImgID varchar(20)," +
            "Name varchar(30)," +
            "Tag varchar(30)," +
            "Description varchar(500)," +
            "Num integer," +
            "Price float," +
            "Profit float," +
            "OddSum integer," +
            "EvenSum integer)";


    public SqlHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS  TABLE_NAME");
        onCreate(sqLiteDatabase);

    }

    public void addGoodsItem(GoodsItem goodsItem) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("ID", goodsItem.getID());
        values.put("ImgID", goodsItem.getImgID());
        values.put("Name", goodsItem.getName());
        values.put("Tag", goodsItem.getTag());
        values.put("Description", goodsItem.getDescription());
        values.put("Num", goodsItem.getNum());
        values.put("Price", goodsItem.getPrice());
        values.put("Profit", goodsItem.getProfit());
        values.put("OddSum", goodsItem.getOddSum());
        values.put("EvenSum", goodsItem.getEvenSum());

        db.insert(TABLE_NAME, null, values);
        db.close();
    }

    public boolean isExistItemByID(String itemID) {
        String strID = "'" + itemID + "'";
        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE ID = " + strID, null);
        if (cursor.getCount() <= 0) {
            cursor.close();
            db.close();
            return false;
        }
        cursor.close();
        db.close();
        return true;
    }

    public GoodsItem getGoodsItemByID(String strID) {
        String selectQuery = "SELECT * from " + TABLE_NAME + " WHERE ID = " + strID;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        GoodsItem goodsItem = null;
        if (cursor.moveToFirst()) {
            goodsItem = new GoodsItem(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
                    cursor.getInt(5), cursor.getFloat(6), cursor.getFloat(7), cursor.getInt(8), cursor.getInt(9));
        }
        cursor.close();
        db.close();
        return goodsItem;
    }

    public int getGoodsItemCount() {
        String selectQuery = "SELECT * from " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        cursor.close();
        db.close();
        return cursor.getCount();

    }

    public List<GoodsItem> getAllGoodsItem() {
        List<GoodsItem> goodsItemsList = new ArrayList<>();
        String selectQuery = "SELECT * FROM  " + TABLE_NAME + " ORDER BY ID ASC";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                GoodsItem goodsItem = new GoodsItem();
                goodsItem.setID(cursor.getString(0));
                goodsItem.setImgID(cursor.getString(1));
                goodsItem.setName(cursor.getString(2));
                goodsItem.setTag(cursor.getString(3));
                goodsItem.setDescription(cursor.getString(4));
                goodsItem.setNum(cursor.getInt(5));
                goodsItem.setPrice(cursor.getFloat(6));
                goodsItem.setProfit(cursor.getFloat(7));
                goodsItem.setOddSum(cursor.getInt(8));
                goodsItem.setEvenSum(cursor.getInt(9));
                goodsItemsList.add(goodsItem);
            }
            while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return goodsItemsList;
    }

    public List<GoodsItem> getGoodsItemsByTag(String tag) {
        List<GoodsItem> goodsItemsList = new ArrayList<>();
        String selectQuery = "SELECT * FROM  " + TABLE_NAME + " WHERE Tag = '" + tag + "'"+" ORDER BY ID ASC";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                GoodsItem goodsItem = new GoodsItem();
                goodsItem.setID(cursor.getString(0));
                goodsItem.setImgID(cursor.getString(1));
                goodsItem.setName(cursor.getString(2));
                goodsItem.setTag(cursor.getString(3));
                goodsItem.setDescription(cursor.getString(4));
                goodsItem.setNum(cursor.getInt(5));
                goodsItem.setPrice(cursor.getFloat(6));
                goodsItem.setProfit(cursor.getFloat(7));
                goodsItem.setOddSum(cursor.getInt(8));
                goodsItem.setEvenSum(cursor.getInt(9));
                goodsItemsList.add(goodsItem);
            }
            while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return goodsItemsList;
    }

    public int updateGoodsItem(GoodsItem goodsItem) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        //       values.put("ID",goodsItem.getID());
        values.put("ImgID", goodsItem.getImgID());
        values.put("Name", goodsItem.getName());
        values.put("Tag", goodsItem.getTag());
        values.put("Description", goodsItem.getDescription());
        values.put("Num", goodsItem.getNum());
        values.put("Price", goodsItem.getPrice());
        values.put("Profit", goodsItem.getProfit());
        values.put("OddSum", goodsItem.getOddSum());
        values.put("EvenSum", goodsItem.getEvenSum());
        db.close();
        return db.update(TABLE_NAME, values, "ID", new String[]{goodsItem.getID()});

    }

    public void updateGoodsItemByColumn(String ID, String columnName, String str) {
        String strID = "'" + ID + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_NAME + " SET " + columnName +" = ' " + str  + " ' WHERE ID = " + strID);
        db.close();
    }

    public void updateGoodsItemByColumn(String ID, String columnName, Integer integer) {
        String strID = "'" + ID + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_NAME + " SET " + columnName +" = " + integer.toString()  + " WHERE ID = " + strID);
        db.close();
    }

    public void updateGoodsItemByColumn(String ID, String columnName, Float f) {
        String strID = "'" + ID + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_NAME + " SET " + columnName +" = " + f.toString()  + " WHERE ID = " + strID);
        db.close();
    }

    public void updateGoodsItemNumByID(String itemID, int num) {
        String strID = "'" + itemID + "'";
        String num_str = Integer.toString(num);
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_NAME + " SET Num = Num + " + num_str + " WHERE ID = " + strID);
        db.close();
    }

    public void deleteGoodsItemByID(String itemID) {
        String strID = "'" + itemID + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DELETE FROM " + TABLE_NAME + " WHERE ID = " + strID);
        db.close();
    }

}
